package com.ibsolutions.epersely;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class TransactionMgr {

	private SQLiteDatabase database;
	private EPerselySQLiteHelper sqliteConn;
	private String[] allTrnColumns = { EPerselySQLiteHelper.TRN_COLUMN_ID,
			EPerselySQLiteHelper.TRN_COLUMN_TYPE,
			EPerselySQLiteHelper.TRN_COLUMN_SUBJECT,
			EPerselySQLiteHelper.TRN_COLUMN_AMOUNT };
	private String[] allFavColumns = { EPerselySQLiteHelper.FAV_COLUMN_ID,
			EPerselySQLiteHelper.FAV_COLUMN_NAME };
	private String[] allMetaColumns = { EPerselySQLiteHelper.META_COLUMN_KEY,
			EPerselySQLiteHelper.META_COLUMN_VALUE };

	// Constructor
	public TransactionMgr(Context context) {
		sqliteConn = new EPerselySQLiteHelper(context);
	}

	// General functions
	public void open() {
		database = sqliteConn.getWritableDatabase();
		Log.i("SQLITE", "Database Opened");
	}

	public void close() {
		sqliteConn.close();
		Log.i("SQLITE", "Database Closed");
	}

	// Transaction functions	
	public void commitTransaction(Transaction transaction) {
		ContentValues values = new ContentValues();
		values.put(EPerselySQLiteHelper.TRN_COLUMN_TYPE, transaction.getType());
		values.put(EPerselySQLiteHelper.TRN_COLUMN_SUBJECT,
				transaction.getSubject());
		values.put(EPerselySQLiteHelper.TRN_COLUMN_AMOUNT,
				transaction.getAmount());
		values.put(EPerselySQLiteHelper.TRN_COLUMN_DATE, transaction.getDate());
		long insertID = database.insert(
				EPerselySQLiteHelper.TABLE_TRANSACTIONS, null, values);
		Cursor cursor = database.query(EPerselySQLiteHelper.TABLE_TRANSACTIONS,
				allTrnColumns, EPerselySQLiteHelper.TRN_COLUMN_ID + " = "
						+ insertID, null, null, null, null);
		cursor.moveToFirst();
		cursor.close();
		Log.i("SQLITE", "Transaction committed  Type:" + transaction.getType());
	}

	public void deleteTransaction() {
		
	}
	
	public List<Transaction> getAllTransaction() {
		List<Transaction> transactions = new ArrayList<Transaction>();

		Cursor cursor = database.query(EPerselySQLiteHelper.TABLE_TRANSACTIONS,
				allTrnColumns, null, null, null, null, null);

		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			Transaction transaction = cursorToTransaction(cursor);
			transactions.add(transaction);
			cursor.moveToNext();
		}
		cursor.close();
		return transactions;
	}

	public List<Transaction> getAllIncome() {
		List<Transaction> incomes = new ArrayList<Transaction>();

		Cursor cursor = database.query(EPerselySQLiteHelper.TABLE_TRANSACTIONS,
				allTrnColumns, EPerselySQLiteHelper.TRN_COLUMN_TYPE
						+ " = \"IN\"", null, null, null, null);

		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			Transaction income = cursorToTransaction(cursor);
			incomes.add(income);
			cursor.moveToNext();
		}
		cursor.close();
		return incomes;
	}

	public List<Transaction> getAllExpense() {
		List<Transaction> expenses = new ArrayList<Transaction>();

		Cursor cursor = database.query(EPerselySQLiteHelper.TABLE_TRANSACTIONS,
				allTrnColumns, EPerselySQLiteHelper.TRN_COLUMN_TYPE
						+ " = \"OUT\"", null, null, null, null);

		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			Transaction expense = cursorToTransaction(cursor);
			expenses.add(expense);
			cursor.moveToNext();
		}
		cursor.close();
		return expenses;
	}
	
	public List<Transaction> getIncomesInPeriod(String startDate, String endDate) {
		List<Transaction> incomes = new ArrayList<Transaction>();
		
		Cursor cursor = database.query(EPerselySQLiteHelper.TABLE_TRANSACTIONS, allTrnColumns, EPerselySQLiteHelper.TRN_COLUMN_TYPE + "=\'IN\' AND " + EPerselySQLiteHelper.TRN_COLUMN_DATE + " BETWEEN ? AND ?", 
				new String[] {startDate, endDate}, EPerselySQLiteHelper.TRN_COLUMN_SUBJECT, 
				null, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			Transaction income = cursorToTransaction(cursor);
			incomes.add(income);
			cursor.moveToNext();
		}
		cursor.close();
		return incomes;
	}
	
	public List<Transaction> getExpensesInPeriod(String startDate, String endDate) {
		List<Transaction> expenses = new ArrayList<Transaction>();
		
		Cursor cursor = database.query(EPerselySQLiteHelper.TABLE_TRANSACTIONS, allTrnColumns, EPerselySQLiteHelper.TRN_COLUMN_TYPE + "=\'OUT\' AND " + EPerselySQLiteHelper.TRN_COLUMN_DATE + " BETWEEN ? AND ?", 
				new String[] {startDate, endDate}, EPerselySQLiteHelper.TRN_COLUMN_SUBJECT, 
				null, null);
		cursor.moveToFirst();
		while(!cursor.isAfterLast()) {
			Transaction expense = cursorToTransaction(cursor);
			expenses.add(expense);
			cursor.moveToNext();
		}
		cursor.close();
		return expenses;
	}

	private Transaction cursorToTransaction(Cursor cursor) {
		Transaction transaction = new Transaction();
		transaction.setId(cursor.getLong(0));
		transaction.setType(cursor.getString(1));
		transaction.setSubject(cursor.getString(2));
		transaction.setAmount(cursor.getInt(3));
		return transaction;
	}

	// Favorite functions
	public void commitFavorite(Favorite favorite) {
		ContentValues values = new ContentValues();
		values.put(EPerselySQLiteHelper.FAV_COLUMN_NAME, favorite.getName());
		long insertID = database.insert(EPerselySQLiteHelper.TABLE_FAVORITES,
				null, values);
		Cursor cursor = database.query(EPerselySQLiteHelper.TABLE_FAVORITES,
				allFavColumns, EPerselySQLiteHelper.FAV_COLUMN_ID + " = "
						+ insertID, null, null, null, null);
		cursor.moveToFirst();
		cursor.close();
	}

	public List<Favorite> getAllFavorite() {
		List<Favorite> favorites = new ArrayList<Favorite>();

		Cursor cursor = database.query(EPerselySQLiteHelper.TABLE_FAVORITES,
				allFavColumns, null, null, null, null, null);

		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			Favorite favorite = cursorToFavorite(cursor);
			favorites.add(favorite);
			cursor.moveToNext();
		}
		cursor.close();
		return favorites;
	}

	public String[] getAllFavAsArray() {
		Cursor cursor = database.query(EPerselySQLiteHelper.TABLE_FAVORITES,
				new String[] { "name" }, null, null, null, null, null);
		String[] favs = new String[cursor.getCount()];
		int i = 0;
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			favs[i] = cursor.getString(0);
			i++;
			cursor.moveToNext();
		}
		return favs;
	}
	
	public void deleteAllFavorites() {
		database.execSQL("delete from " + EPerselySQLiteHelper.TABLE_FAVORITES + ";");
		Log.i("SQLITE", "Favorites table was cleared");
	}
	
	public void deleteFavorite(String favorite) {
		database.delete(EPerselySQLiteHelper.TABLE_FAVORITES, EPerselySQLiteHelper.FAV_COLUMN_NAME + " = \"" + favorite + "\";", null);
		Log.i("SQLITE", "Deleted favorite: " + favorite);
	}
	
	public void updateFavorite(String oldName, String newName) {
		ContentValues favValues = new ContentValues();
		ContentValues trnValues = new ContentValues();
		favValues.put(EPerselySQLiteHelper.FAV_COLUMN_NAME, newName);
		trnValues.put(EPerselySQLiteHelper.TRN_COLUMN_SUBJECT, newName);
		database.update(EPerselySQLiteHelper.TABLE_FAVORITES, favValues, EPerselySQLiteHelper.FAV_COLUMN_NAME + " = \"" + oldName + "\";", null);
		database.update(EPerselySQLiteHelper.TABLE_TRANSACTIONS, trnValues, EPerselySQLiteHelper.TRN_COLUMN_SUBJECT + " = \"" + oldName + "\";", null);
	}

	private Favorite cursorToFavorite(Cursor cursor) {
		Favorite favorite = new Favorite();
		favorite.setId(cursor.getLong(0));
		favorite.setName(cursor.getString(1));
		return favorite;
	}

	// Meta functions
	public void updateMeta(String key, int value) {
		database.execSQL("update meta set value=" + value + " where _key=\""
				+ key + "\";");
	}

	public int getFirstValue() {
		Cursor cursor = database.query(EPerselySQLiteHelper.TABLE_METADATA,
				allMetaColumns, "_key=\"first\"", null, null, null, null);
		cursor.moveToFirst();
		int first = cursor.getInt(1);
		cursor.close();
		return first;
	}

	public float getBudget() {
		Cursor cursor = database.query(EPerselySQLiteHelper.TABLE_METADATA,
				allMetaColumns, "_key=\"budget\"", null, null, null, null);
		cursor.moveToFirst();
		float budget = cursor.getFloat(1);
		cursor.close();
		return budget;
	}

	public void updateBudget(String type, float value) {
		if (type.equals("IN")) {
			database.execSQL("update meta set value = value +" + value
					+ " where _key=\"budget\";");
		} else if (type.equals("OUT")) {
			database.execSQL("update meta set value = value -" + value
					+ " where _key=\"budget\";");
		}
	}
	
	// Statistics functions
	private PieKeyValue cursorToPieKeyValue(Cursor cursor) {
		PieKeyValue pieKeyValue = new PieKeyValue();
		pieKeyValue.setKey(cursor.getString(0));
		pieKeyValue.setValue(cursor.getInt(1));
		return pieKeyValue;
	}
		
	public ArrayList<PieKeyValue> getExpenseInPie(String startDate, String endDate) {
		ArrayList<PieKeyValue> datas = new ArrayList<PieKeyValue>();
		Cursor cursor = database.query(EPerselySQLiteHelper.TABLE_TRANSACTIONS, 
				new String[] {"subject","SUM(amount)"}, 
				EPerselySQLiteHelper.TRN_COLUMN_TYPE + "=\'OUT\' AND " + EPerselySQLiteHelper.TRN_COLUMN_DATE + " BETWEEN ? AND ?", 
				new String[] {startDate, endDate}, EPerselySQLiteHelper.TRN_COLUMN_SUBJECT, 
				null, null);
		cursor.moveToFirst();
		while(!cursor.isAfterLast()) {
			PieKeyValue pieKeyValue = cursorToPieKeyValue(cursor);
			datas.add(pieKeyValue);
			cursor.moveToNext();						
		}
		cursor.close();
		return datas;
	}
	
	public ArrayList<PieKeyValue> getIncomeInPie(String startDate, String endDate) {
		ArrayList<PieKeyValue> datas = new ArrayList<PieKeyValue>();
		Cursor cursor = database.query(EPerselySQLiteHelper.TABLE_TRANSACTIONS, 
				new String[] {"subject","SUM(amount)"}, 
				EPerselySQLiteHelper.TRN_COLUMN_TYPE + "=\'IN\' AND " + EPerselySQLiteHelper.TRN_COLUMN_DATE + " BETWEEN ? AND ?", 
				new String[] {startDate, endDate}, EPerselySQLiteHelper.TRN_COLUMN_SUBJECT, 
				null, null);
		cursor.moveToFirst();
		while(!cursor.isAfterLast()) {
			PieKeyValue pieKeyValue = cursorToPieKeyValue(cursor);
			datas.add(pieKeyValue);
			cursor.moveToNext();						
		}
		cursor.close();
		return datas;
	}
}
